﻿using Lamex.Base;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Lamex.mMES.Common
{
    public class GetDataTable
    {
        //获取异常条码
        public static DataTable GetWork_Barcode()
        {
            DataTable dt = new DataTable();
            string sql = @"SELECT [BARCODE_SN]
      ,[LIGHT_STATE]
      ,[BARCODE_TYPE]
      ,[BARCODE_DESC]    
  FROM [LamexMESManager].[dbo].[HR_Work_Barcode]";
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
        //获取加工产品明细
        public static DataTable GetPart_Detailed()
        {
            DataTable dt = new DataTable();
            string sql = @"SELECT  [Order_Number] FROM [LamexMESManager].[dbo].[Transfer_Part_Detailed]";
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
        //获取工号信息
        public static DataTable GetWorksInfo(string WORKNO)
        {
            DataTable dt = new DataTable();
            string sql = @"SELECT [OPRATION_CODE],[WORKER_NAME]    
  FROM [LamexMESManager].[dbo].[HR_Workers_Info]
  where WORKER_NO='" + WORKNO + "' ";
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
        //获取站点条码对应姓名
        public static DataTable GetWorkName(string LOCATION_NAME, string STATION_NAME)
        {
            DataTable dt = new DataTable();
            string sql = @"  select W.WORKER_NAME
 from LamexMESManager.dbo.HR_Location_Record L
 LEFT JOIN LamexMESManager.dbo.HR_Station_Info S ON L.LOCATION_CODE=S.LOCATION_CODE
 LEFT JOIN LamexMESManager.dbo.HR_Workers_Info W ON L.DUTY_OPERATOR=W.WORKER_NO
 where LOCATION_NAME='" + LOCATION_NAME + "' and STATION_NAME='" + STATION_NAME + "'";
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
        //获取当天第一个码
        public static DataTable GetFirstBarCode(string firstTime, string lastTime, string STATION_NAME)
        {
            DataTable dt = new DataTable();
            string sql = @" select Top 1 BARCODE FROM [LamexMESManager].[dbo].[HR_Scan_Record]
                                             where SCAN_TIME>'" + firstTime + "' and SCAN_TIME<'" + lastTime + "' and ISNULL(CANCEL_FLAG,'')!='1' AND BARCODE!='MES_MG11111' and NOID=0 and STATION_NAME='" + STATION_NAME + "'  order by SCAN_TIME asc";
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
        //获取当天最后一个条码
        public static DataTable GetLastBarCode(string firstTime, string lastTime, string STATION_NAME)
        {
            DataTable dt = new DataTable();
            string sql = @" select Top 1 BARCODE FROM [LamexMESManager].[dbo].[HR_Scan_Record]
                                             where SCAN_TIME>'" + firstTime + "' and SCAN_TIME<'" + lastTime + "' and ISNULL(CANCEL_FLAG,'')!='1' AND BARCODE!='MES_MG11111' and NOID=0 and STATION_NAME='" + STATION_NAME + "' order by SCAN_TIME desc";
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
        //获取最后两个条码【cancel!=1】
        public static DataTable GetNoCancel_Top2(string firstTime, string STATION_NAME)
        {
            DataTable dt = new DataTable();
            string sql_S08 = @"select TOP 2 * FROM [LamexMESManager].[dbo].[HR_Scan_Record]
                                                 where (ISNULL(NOID,'')='' or NOID=0) and SCAN_TIME>'" + firstTime + "' and ISNULL(CANCEL_FLAG,'')!='1' and STATION_NAME='" + STATION_NAME + "' order by SCAN_TIME desc";
            dt = BaseBuildSql.GetDataTableBySql(sql_S08);
            return dt;
        }
        //获取最后两个条码【cancel!=1 and barcode!='MES_MG11111'】
        public static DataTable GetNoCancelNoMG_Top2(string firstTime, string STATION_NAME)
        {
            DataTable dt = new DataTable();
            string sql_S08 = @"select TOP 2 * FROM [LamexMESManager].[dbo].[HR_Scan_Record]
                                                 where (ISNULL(NOID,'')='' or NOID=0) and SCAN_TIME>'" + firstTime + "' and ISNULL(CANCEL_FLAG,'')!='1' AND BARCODE!='MES_MG11111' AND BARCODE not like '%90000' and STATION_NAME='" + STATION_NAME + "' order by SCAN_TIME desc";
            dt = BaseBuildSql.GetDataTableBySql(sql_S08);
            return dt;
        }
        //获取最后一个ORDER_NUMBER时间值
        public static DataTable GetOrder_Number_Time(string firstTime, string lastTime, string STATION_NAME)
        {
            DataTable dt = new DataTable();
            string sql = @"SELECT TOP 1 SCAN_TIME  FROM [LamexMESManager].[dbo].[HR_Scan_Record]
 WHERE (ISNULL(NOID,'')='' or NOID=0) and ISNULL(CANCEL_FLAG,'')!='1' and SCAN_TIME>'" + firstTime + "' and SCAN_TIME<'" + lastTime + "' and STATION_NAME='" + STATION_NAME + "' and (BARCODE like 'DK%' or BARCODE like 'PL%' or BARCODE like 'PN%' or BARCODE like 'MG%')   ORDER BY SCAN_TIME DESC";
            DataTable dt_Temp = BaseBuildSql.GetDataTableBySql(sql);
            if (dt_Temp.Rows.Count <= 0)
            {
                //找MES_MG00000的时间
                string sql_MG = @"SELECT TOP 1 SCAN_TIME  FROM [LamexMESManager].[dbo].[HR_Scan_Record]
 WHERE (ISNULL(NOID,'')='' or NOID=0) and ISNULL(CANCEL_FLAG,'')!='1' and SCAN_TIME>'" + firstTime + "' and SCAN_TIME<'" + lastTime + "' AND BARCODE='MES_MG00000' and STATION_NAME='" + STATION_NAME + "' ORDER BY SCAN_TIME DESC ";
                dt = BaseBuildSql.GetDataTableBySql(sql_MG);
            }
            else
            {
                dt = dt_Temp;
            }
            return dt;
        }
        //获取站点管理表
        public static DataTable GetHR_Station_Info()
        {
            DataTable dt = new DataTable();
            string sql = @"SELECT [DEPARTMENT]
      ,[STATION_NAME]
      ,[PROCESS_CODE]
      ,[LOCATION_CODE]
      ,[LOCATION_NAME]
      ,[DEVEICE_COM]
      ,[UPDATE_TIME]
      ,[UPDATE_USER]
  FROM [LamexMESManager].[dbo].[HR_Station_Info]";
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
        //获取当天包含“MES_MG00000”的记录
        public static DataTable GetHR_Scan_Record_MG00000(string firstTime, string lastTime, string STATION_NAME)
        {
            DataTable dt = new DataTable();
            string sql = @" SELECT BARCODE FROM [LamexMESManager].[dbo].[HR_Scan_Record]  where BARCODE='MES_MG00000' and ISNULL(CANCEL_FLAG,'')!='1'
         and STATION_NAME='" + STATION_NAME + "'  and SCAN_TIME>'" + firstTime + "' and SCAN_TIME<'" + lastTime + "'";
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
        //获取当前工作站最后一个条码
        public static DataTable GetLastBarCode_StationName(string STATION_NAME)
        {
            DataTable dt = new DataTable();
            string sql = @" SELECT top 1 BARCODE FROM [LamexMESManager].[dbo].[HR_Scan_Record]
 where  ISNULL(CANCEL_FLAG,'')!='1' AND BARCODE!='MES_MG11111' AND BARCODE not like '%90000' AND
STATION_NAME='" + STATION_NAME + "' and  (ISNULL(NOID,'')='' or NOID=0) order by SCAN_TIME desc";
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }

    }
}
